﻿use master
GO
drop database QLKH_DANGKYSUDUNGINTERNET
GO
create database QLKH_DANGKYSUDUNGINTERNET
GO
use QLKH_DANGKYSUDUNGINTERNET
GO
-------------- Khoi tao database
if exists
(
    select name from sysobjects
    where name = 'Init_Database' and type = 'p'
)
begin
 drop proc Init_Database
end;
GO
create proc Init_Database
as
BEGIN
	DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
	SET @Cursor = CURSOR FAST_FORWARD FOR
	SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
	FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
	LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
	OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	Exec SP_EXECUTESQL @Sql
	FETCH NEXT FROM @Cursor INTO @Sql
	END
	CLOSE @Cursor DEALLOCATE @Cursor
	EXEC sp_MSForEachTable 'DROP TABLE ?'
END
GO
EXEC Init_Database
------------------------------------------------------
GO
----------------------------------------------------------
--1. Create table and its columns
CREATE TABLE [dbo].[CT_CAIDAT](
	[MaHTCD] [nvarchar](50) NOT NULL,
	[MaDV] [nvarchar](50) NOT NULL);
GO
--2. Create table and its columns
CREATE TABLE [dbo].[CT_LICHSUDICHVU](
	[MaLoaiLuuLuong] [nvarchar](50) NOT NULL,
	[MaLSDV] [nvarchar](50) NOT NULL);
GO

--3. Create table and its columns
CREATE TABLE [dbo].[CT_LOAILUULUONG](
	[MaGoiCuoc] [int] NOT NULL,
	[MaLoaiLuuLuong] [nvarchar](50) NOT NULL,
	[GiaCuocTinhTrenMB] [nvarchar](50) NOT NULL);	
GO

--4. Create table and its columns 
CREATE TABLE [dbo].[DICHVU](
	[MaDV] [int] NOT NULL,
	[MaHopDong] [int] NOT NULL,
	[TenDichVu] [nvarchar](200) NOT NULL,
	[TinhTrangDichVu] [nvarchar](200) NOT NULL,
	[DiaChiCaiDat] [nvarchar](512) NOT NULL,
	[DiaChiHoaDon] [nvarchar](1000) NULL,
	[SoLuongTaiKhoan] [int] NULL,
	[NgayDangKy] [date] NOT NULL);
GO

--5. Create table and its columns
CREATE TABLE [dbo].[HINHTHUCCAIDAT](
	[MaHTCD] [nvarchar](50) NOT NULL,
	[MaKieuCaiDat] [nvarchar](50) NOT NULL,
	[MaModem] [nvarchar](50) NOT NULL,
	[TenHTCD] [nvarchar](50) NULL);
GO

--6. Create table and its columns
CREATE TABLE [dbo].[GOICUOC](
	[MaGoiCuoc] [int] NOT NULL,
	[TenGoiCuoc] [nvarchar](50) NOT NULL,
	[LoaiGoiCuoc] [nvarchar](50) NOT NULL);
GO
--7. Create table and its columns
CREATE TABLE [dbo].[HOADONLAPDAT](
	[MaHDLD] [int] NOT NULL,
	[NgayLapDat] [date] NOT NULL,
	[Phi] [nvarchar](50) NOT NULL,
	[TinhTrangThanhToan] [nvarchar](50) NOT NULL);
GO
--8. Create table and its columns
CREATE TABLE [dbo].[HOADONTHANHTOAN](
	[MaHDTT] [int] NOT NULL,
	[CuocTuNgay] [date] NOT NULL,
	[CuocDenNgay] [date] NOT NULL,
	[TinhTrangThanhToan] [nvarchar](50) NOT NULL,
	[CuocPhi] [nvarchar](50) NOT NULL);
GO

--9. Create table and its columns
CREATE TABLE [dbo].[HOPDONG](
	[MaHopDong] [int] NOT NULL,
	[MaKhachHang] [int] NOT NULL,
	[NgayDangKy] [date] NOT NULL);
GO

--10. Create table and its columns
CREATE TABLE [dbo].[KHACHHANG](
	[MaKhachHang] [int] NOT NULL,
	[HoTen] [nvarchar](30) NOT NULL,
	[CMND] [int] NOT NULL,
	[Email] [nvarchar](50) NULL,
	[DiaChi] [nvarchar](100) NOT NULL,
	[DienThoai] [int] NOT NULL
);
GO

--11. Create table and its columns
CREATE TABLE [dbo].[KHACHHANGTHANHTOANTRE](
	[MaKHTTT] [nvarchar](50) NOT NULL,
	[MaLSDV] [int] NOT NULL);
GO
--12. Create table and its columns
CREATE TABLE [dbo].[KHUYENMAI](
	[MaKhuyenMai] [varchar](50) NOT NULL,
	[TenKhuyenMai] [nvarchar](50) NOT NULL,
	[KhuyenMaiTuNgay] [date] NOT NULL,
	[KhuyenMaiDenNgay] [date] NOT NULL,
	[LoaiKhachHangKhuyenMai] [nvarchar](50) NOT NULL,
	[NoiDungKhuyenMai] [nvarchar](50) NOT NULL
);

GO
--13. Create table and its columns
CREATE TABLE [dbo].[KIEUCAIDAT](
	[MaKieuCaiDat] [nvarchar](50) NOT NULL,
	[TenKieuCaiDat] [nvarchar](50) NULL
);

GO
--14. Create table and its columns
CREATE TABLE [dbo].[LICHSUDICHVU](
	[MaLSDV] [nvarchar](20) NOT NULL,
	[MaDV] [int] NOT NULL,
	[Thang] [date] NOT NULL,
	[Nam] [date] NOT NULL,
	[LuuLuongSuDung] [nvarchar](50) NOT NULL
);

GO
--15. Create table and its columns
CREATE TABLE [dbo].[LOAILUULUONG](
	[MaLoaiLuuLuong] [nvarchar](50) NOT NULL,
	[TenLoaiLuuLuong] [nvarchar](50) NULL
);

GO
--16. Create table and its columns
CREATE TABLE [dbo].[LOAINGUOIDUNG](
	[MaLoaiNguoiDung] [varchar](50) NOT NULL,
	[TenLoaiNguoiDung] [nvarchar](50) NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL
);

GO
--17. Create table and its columns
CREATE TABLE [dbo].[MODEM](
	[MaModem] [int] NOT NULL,
	[TenModem] [nvarchar](50) NULL
);

GO
--18. Create table and its columns
CREATE TABLE [dbo].[NGUOIDUNG](
	[MaNguoiDung] [int] NOT NULL,
	[MaLoaiNguoiDung] [nvarchar](50) NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[Password] [varbinary](50) NOT NULL
);

GO
--19. Create table and its columns
CREATE TABLE [dbo].[TAIKHOANDANGNHAP](
	[MaTaiKhoan] [int] NOT NULL,
	[MaDV] [int] NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL
);

GO
----------------------------------------------------------
--PRIMARY KEY
----------------------------------------------------------
--1. Primary key KHACHHANG
ALTER TABLE [dbo].[KHACHHANG] ADD CONSTRAINT [PK_KHACHHANG] PRIMARY KEY ([MaKhachHang]) 
GO

--2. Primary key 